SQLに対応するRスクリプト例

データ例としてToothGrowthを用いる

• len:歯の長さ
• supp:サプリメントの種類(VC:ビタミンC,OJ:オレンジジュース)
• dose:サプリメントの量(mg)

head(ToothGrowth)

##    len supp dose
## 1  4.2   VC  0.5
## 2 11.5   VC  0.5
## 3  7.3   VC  0.5
## 4  5.8   VC  0.5
## 5  6.4   VC  0.5
## 6 10.0   VC  0.5

tmp <- data.frame(supp = c("VC", "OJ"), supp.name = c("Vitamin C", "Orange Juice"))
tmp

##   supp    supp.name
## 1   VC    Vitamin C
## 2   OJ Orange Juice

SELECT * FROM ToothGrowth WHERE supp=“OJ” AND dose=0.5　に対応するRスクリプト

subset(ToothGrowth, supp == "OJ" & dose == 0.5)

##     len supp dose
## 31 15.2   OJ  0.5
## 32 21.5   OJ  0.5
## 33 17.6   OJ  0.5
## 34  9.7   OJ  0.5
## 35 14.5   OJ  0.5
## 36 10.0   OJ  0.5
## 37  8.2   OJ  0.5
## 38  9.4   OJ  0.5
## 39 16.5   OJ  0.5
## 40  9.7   OJ  0.5

SELECT * FROM ToothGrowth WHERE dose==0.5 OR dose==1.0　に対応するRスクリプト

subset(ToothGrowth, dose == 0.5 | dose == 1)

##     len supp dose
## 1   4.2   VC  0.5
## 2  11.5   VC  0.5
## 3   7.3   VC  0.5
## 4   5.8   VC  0.5
## 5   6.4   VC  0.5
## 6  10.0   VC  0.5
## 7  11.2   VC  0.5
## 8  11.2   VC  0.5
## 9   5.2   VC  0.5
## 10  7.0   VC  0.5
## 11 16.5   VC  1.0
## 12 16.5   VC  1.0
## 13 15.2   VC  1.0
## 14 17.3   VC  1.0
## 15 22.5   VC  1.0
## 16 17.3   VC  1.0
## 17 13.6   VC  1.0
## 18 14.5   VC  1.0
## 19 18.8   VC  1.0
## 20 15.5   VC  1.0
## 31 15.2   OJ  0.5
## 32 21.5   OJ  0.5
## 33 17.6   OJ  0.5
## 34  9.7   OJ  0.5
## 35 14.5   OJ  0.5
## 36 10.0   OJ  0.5
## 37  8.2   OJ  0.5
## 38  9.4   OJ  0.5
## 39 16.5   OJ  0.5
## 40  9.7   OJ  0.5
## 41 19.7   OJ  1.0
## 42 23.3   OJ  1.0
## 43 23.6   OJ  1.0
## 44 26.4   OJ  1.0
## 45 20.0   OJ  1.0
## 46 25.2   OJ  1.0
## 47 25.8   OJ  1.0
## 48 21.2   OJ  1.0
## 49 14.5   OJ  1.0
## 50 27.3   OJ  1.0

SELECT * FROM ToothGrowth ORDER BY supp, dose　に対応するRスクリプト

ToothGrowth[order(ToothGrowth$supp, ToothGrowth$dose), ]

##     len supp dose
## 31 15.2   OJ  0.5
## 32 21.5   OJ  0.5
## 33 17.6   OJ  0.5
## 34  9.7   OJ  0.5
## 35 14.5   OJ  0.5
## 36 10.0   OJ  0.5
## 37  8.2   OJ  0.5
## 38  9.4   OJ  0.5
## 39 16.5   OJ  0.5
## 40  9.7   OJ  0.5
## 41 19.7   OJ  1.0
## 42 23.3   OJ  1.0
## 43 23.6   OJ  1.0
## 44 26.4   OJ  1.0
## 45 20.0   OJ  1.0
## 46 25.2   OJ  1.0
## 47 25.8   OJ  1.0
## 48 21.2   OJ  1.0
## 49 14.5   OJ  1.0
## 50 27.3   OJ  1.0
## 51 25.5   OJ  2.0
## 52 26.4   OJ  2.0
## 53 22.4   OJ  2.0
## 54 24.5   OJ  2.0
## 55 24.8   OJ  2.0
## 56 30.9   OJ  2.0
## 57 26.4   OJ  2.0
## 58 27.3   OJ  2.0
## 59 29.4   OJ  2.0
## 60 23.0   OJ  2.0
## 1   4.2   VC  0.5
## 2  11.5   VC  0.5
## 3   7.3   VC  0.5
## 4   5.8   VC  0.5
## 5   6.4   VC  0.5
## 6  10.0   VC  0.5
## 7  11.2   VC  0.5
## 8  11.2   VC  0.5
## 9   5.2   VC  0.5
## 10  7.0   VC  0.5
## 11 16.5   VC  1.0
## 12 16.5   VC  1.0
## 13 15.2   VC  1.0
## 14 17.3   VC  1.0
## 15 22.5   VC  1.0
## 16 17.3   VC  1.0
## 17 13.6   VC  1.0
## 18 14.5   VC  1.0
## 19 18.8   VC  1.0
## 20 15.5   VC  1.0
## 21 23.6   VC  2.0
## 22 18.5   VC  2.0
## 23 33.9   VC  2.0
## 24 25.5   VC  2.0
## 25 26.4   VC  2.0
## 26 32.5   VC  2.0
## 27 26.7   VC  2.0
## 28 21.5   VC  2.0
## 29 23.3   VC  2.0
## 30 29.5   VC  2.0

SELECT supp, dose, AVG(*) FROM ToothGrowth GROUP BY supp, dose　に対応するRスクリプト

aggregate(len ~ supp + dose, data = ToothGrowth, mean)

##   supp dose   len
## 1   OJ  0.5 13.23
## 2   VC  0.5  7.98
## 3   OJ  1.0 22.70
## 4   VC  1.0 16.77
## 5   OJ  2.0 26.06
## 6   VC  2.0 26.14

SELECT * FROM ToothGrowth, tmp WHERE ToothGrowth.supp = tmp.supp　に対応するRスクリプト

merge(ToothGrowth, tmp, by = "supp")

##    supp  len dose    supp.name
## 1    OJ 15.2  0.5 Orange Juice
## 2    OJ 21.5  0.5 Orange Juice
## 3    OJ 17.6  0.5 Orange Juice
## 4    OJ  9.7  0.5 Orange Juice
## 5    OJ 14.5  0.5 Orange Juice
## 6    OJ 10.0  0.5 Orange Juice
## 7    OJ  8.2  0.5 Orange Juice
## 8    OJ  9.4  0.5 Orange Juice
## 9    OJ 16.5  0.5 Orange Juice
## 10   OJ  9.7  0.5 Orange Juice
## 11   OJ 19.7  1.0 Orange Juice
## 12   OJ 23.3  1.0 Orange Juice
## 13   OJ 23.6  1.0 Orange Juice
## 14   OJ 26.4  1.0 Orange Juice
## 15   OJ 20.0  1.0 Orange Juice
## 16   OJ 25.2  1.0 Orange Juice
## 17   OJ 25.8  1.0 Orange Juice
## 18   OJ 21.2  1.0 Orange Juice
## 19   OJ 14.5  1.0 Orange Juice
## 20   OJ 27.3  1.0 Orange Juice
## 21   OJ 25.5  2.0 Orange Juice
## 22   OJ 26.4  2.0 Orange Juice
## 23   OJ 22.4  2.0 Orange Juice
## 24   OJ 24.5  2.0 Orange Juice
## 25   OJ 24.8  2.0 Orange Juice
## 26   OJ 30.9  2.0 Orange Juice
## 27   OJ 26.4  2.0 Orange Juice
## 28   OJ 27.3  2.0 Orange Juice
## 29   OJ 29.4  2.0 Orange Juice
## 30   OJ 23.0  2.0 Orange Juice
## 31   VC  4.2  0.5    Vitamin C
## 32   VC 11.5  0.5    Vitamin C
## 33   VC  7.3  0.5    Vitamin C
## 34   VC  5.8  0.5    Vitamin C
## 35   VC  6.4  0.5    Vitamin C
## 36   VC 10.0  0.5    Vitamin C
## 37   VC 11.2  0.5    Vitamin C
## 38   VC 11.2  0.5    Vitamin C
## 39   VC  5.2  0.5    Vitamin C
## 40   VC  7.0  0.5    Vitamin C
## 41   VC 16.5  1.0    Vitamin C
## 42   VC 16.5  1.0    Vitamin C
## 43   VC 15.2  1.0    Vitamin C
## 44   VC 17.3  1.0    Vitamin C
## 45   VC 22.5  1.0    Vitamin C
## 46   VC 17.3  1.0    Vitamin C
## 47   VC 13.6  1.0    Vitamin C
## 48   VC 14.5  1.0    Vitamin C
## 49   VC 18.8  1.0    Vitamin C
## 50   VC 15.5  1.0    Vitamin C
## 51   VC 23.6  2.0    Vitamin C
## 52   VC 18.5  2.0    Vitamin C
## 53   VC 33.9  2.0    Vitamin C
## 54   VC 25.5  2.0    Vitamin C
## 55   VC 26.4  2.0    Vitamin C
## 56   VC 32.5  2.0    Vitamin C
## 57   VC 26.7  2.0    Vitamin C
## 58   VC 21.5  2.0    Vitamin C
## 59   VC 23.3  2.0    Vitamin C
## 60   VC 29.5  2.0    Vitamin C